package net.hn.hnms.biz.mine.company.service;

import lombok.RequiredArgsConstructor;
import net.hn.hnms.biz.api.mine.model.ConnectionCountVO;
import net.hn.hnms.biz.api.mine.model.ConnectionState;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 智呈物联 pan'sx
 * 2022/12/26 8:44
 * 1.0 version
 */
@Service
@RequiredArgsConstructor
public class CompanyService {
    private final JdbcTemplate jdbcTemplate;
    Map<String, String> SCHEMACODE_NAME_MAP = new HashMap<String, String>() {
        {
            put("aqjk", "安全监控");
            put("shfz", "水害防治");
            put("hzfz", "火灾防治");
            put("dbzh", "顶板灾害");
            put("cjdy", "冲击地压");
            put("fcfz", "粉尘防治");
            put("jxry", "井下人员");
            put("swyt", "风险隐患");
            put("wscl", "污水处理");
            put("yjgl", "应急管理");
        }
    };
    public List<ConnectionState> connectionState( String schema) {
        String sql = "SELECT t1.mine_name,\n" +
                "CASE t1.upload_state\n" +
                "	WHEN '0' THEN '未上传'\n" +
                "	when '1' THEN '已上传'\n" +
                "	when '2' THEN '一时断开'\n" +
                "	when '3' THEN '永久断开'\n" +
                "	ELSE '未知'\n" +
                "END  as state\n" +
                " FROM \"dws_comm_now_connection\" t1 JOIN\n" +
                "(SELECT mine_code,max(upload_time) upload_time FROM \"dws_comm_now_connection\" where schema_code='%s' GROUP BY mine_code) t2\n" +
                "on  t1.mine_code=t2.mine_code and t1.upload_time=t2.upload_time where    t1.schema_code='%s'";
        return jdbcTemplate.query(String.format(sql, schema, schema), new Object[]{}, new BeanPropertyRowMapper<ConnectionState>(ConnectionState.class));
    }
    public List<ConnectionCountVO> connectionCount() {
        String sql = "select schema_code as schemaCode, sum(case state when '在线' then 1 else 0 end) as onlineCount,\n" +
                "sum(case state when '中断' then 1 else 0 end) as breakCount, count(mine_code) as connectedCount\n" +
                "from\n" +
                "(\n" +
                "select t1.schema_code, t1.mine_code, \n" +
                "CASE t1.upload_state\n" +
                "\tWHEN '0' THEN '在线'\n" +
                "\twhen '1' THEN '在线'\n" +
                "\twhen '-1' THEN '中断'\n" +
                "\twhen '-2' THEN '中断'\n" +
                "\tELSE '未知'\n" +
                "END  as state\n" +
                "FROM \"dws_comm_now_connection\" t1 JOIN\n" +
                "(SELECT mine_code,max(upload_time) upload_time, schema_code FROM \"dws_comm_now_connection\" GROUP BY mine_code, schema_code ) t2\n" +
                "on  t1.mine_code=t2.mine_code and t1.upload_time=t2.upload_time and t1.schema_code= t2.schema_code) rst\n" +
                "group by schema_code";
        StringBuilder sb = new StringBuilder(sql);
        List<ConnectionCountVO> result = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ConnectionCountVO.class));
        result.forEach(r -> r.setSchemaName(SCHEMACODE_NAME_MAP.get(r.getSchemaCode())));
        return result;
    }
}
